Queries that run against very large data warehouses are complex. The execution time of the queries can range from seconds to hours, or even to days. The long-running queries can require the use of memory-intensive operators, such as sort and/or hash-join. The operators use system memory to sort their input data and/or to join their data sets. The amount of system memory allocated to the operators affects the query's performance, such as the elapsed time. Typically, the larger the system memory space that is available for executing a query, the better performance the query achieves. However, there is only limited system memory available in a computer system, and the system memory is shared by all concurrent queries and operators. Therefore, a challenge for database systems is to design an effective strategy to manage query concurrency levels given limited system memory resources.
Effectively managing of query concurrency levels on a database system can be a complex task. If too many concurrent requests are allowed to execute, the database system will perform poorly because of resource contention, e.g., memory resource contention. Uncontrolled high contention on system memory resources can cause severe performance problems, such as out of memory (OOM) errors, occurring in the computer system and can result in a database crash. On the other hand, if too few queries are allowed to execute, then system memory resources can be under-utilized and result in a long queue of queries waiting to be executed, thereby causing the database system performance to suffer.
A typical approach of commercial database systems to managing query concurrency levels is the use of threshold-based system configuration parameters, such as multi-programming levels (MPLs). MPLs specify the upper limit of the number of queries that are allowed to run concurrently in a database system. If the number of concurrent queries reaches the threshold, the system will reject newly arriving queries being admitted. This type of approach relies on database administrators to supply an “optimal” setting for the configuration parameter. However, while setting a static threshold for the number of queries that are allowed to execute concurrently may work well if queries are relatively equal in their resource requirements, this can lead to low performance or severe performance problems if the queries are extremely varied such as the ones present in a large data warehouse environment.